﻿Imports System.Data.SqlClient
Imports System.Data.Odbc

Public Class frmCatalogoRutasDetalle
    Dim Rutas As New clsCatalogoRutas
    Dim Bitacora As New clsActividad
    Dim Usuario As New clsUsuarioPrincipal
    Private mstrCondicion As String
    Public WriteOnly Property Condicion() As String
        Set(ByVal value As String)
            mstrCondicion = value
        End Set
    End Property

    Private Sub frmCatalogoRutasDetalle_FormClosed(sender As Object, e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        Bitacora.RegistraActividad("Cerró al catálogo detallado de rutas")
        Usuario.Ubicacion("Catálogo de rutas")
        frmCatalogoRutas.CargaRutas()
    End Sub
    Private Sub frmCatalogoRutasDetalle_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        ToolStrip1.Cursor = Cursors.Hand
        ToolStrip1.RenderMode = System.Windows.Forms.ToolStripRenderMode.System

        If RutaNueva = True Then
            txtRuta.Text = ""
        Else
            txtRuta.Text = RutaSelect
        End If

        'Actualizar()
        CargaOptocontrol()
        CargaRutas()
        CargaClientes()


        Bitacora.RegistraActividad("Ingresó al catálogo detallado de rutas")
        Usuario.Ubicacion("Catálogo detallado de rutas")

    End Sub
    Sub Actualizar()
        Dim strNombre As String = ""
        If txtRuta.Text <> "" Then
            strNombre = Me.txtRuta.Text
            Rutas.Nombre = strNombre
        Else
            strNombre = ""
            Rutas.Nombre = strNombre
        End If
        Rutas.Buscar()
        Scatter()
    End Sub
    Private Sub Scatter()
        txtKms.Text = Rutas.Kilometros
        txtAdminPAQ.Text = Rutas.cCodigoV01
        txtMapa.Text = Rutas.Imagen
        txtBuscarOptocontrol.Text = ""
        txtBuscarRuta.Text = ""
        txtOptocontrol.Text = ""
        txtCliente.Text = BuscarStrDatoCitra("MGW10002", "cRazonSo01", "cCodigoC01='" & Rutas.Texto01 & "'")
        txtHoras.Text = Rutas.Importe01
        CargaImagen()
        CargaRutasDefinidas()
    End Sub
    Sub CargaImagen()
        pbMapa.Image = Nothing
        Dim imagen As String = txtMapa.Text
        Try
            pbMapa.Image = Image.FromFile(imagen)
        Catch ex As Exception
        End Try
    End Sub

    Private Sub txtRuta_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtRuta.TextChanged
        Actualizar()
    End Sub
    Sub CargaClientes()
        Windows.Forms.Cursor.Current = Cursors.WaitCursor
        DgProductos.Rows.Clear()

        Dim Filtro As String
        Filtro = UCase(txtBuscarCliente.Text)
        Filtro = "%" & Filtro.Replace(" ", "%") & "%"

        Dim cnConn As New SqlConnection
        Dim strSql As String
        Dim cmdBuscar As New SqlCommand
        Dim rdBuscar As SqlDataReader

        Try
            cnConn.ConnectionString = CitraConnection
            strSql = "Select cCodigoC01,cRazonSo01 from MGW10002 WHERE cTipoCli01=1 and cEstatus=1 AND (UPPER(cCodigoc01) LIKE '" & Filtro & "' " & mstrCondicion & " OR UPPER(cRazonSo01) LIKE '" & Filtro & "' " & mstrCondicion & ")"

            cmdBuscar.Connection = cnConn
            cmdBuscar.CommandText = strSql


            cnConn.Open()
            rdBuscar = cmdBuscar.ExecuteReader

            Do While rdBuscar.Read()
                DgProductos.Rows.Add(rdBuscar("cCodigoC01").ToString.Trim, rdBuscar("cRazonSo01").ToString.Trim)
            Loop
        Catch ex As Exception
            MessageBox.Show(ex.Message, "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cnConn.State = ConnectionState.Open Then
                cnConn.Close()
            End If
        End Try
    End Sub
    Sub CargaRutasDefinidas()
        dgOptocontrol.Rows.Clear()

        Dim cnConn As New SqlConnection
        Dim strSql As String
        Dim cmdBuscar As New SqlCommand
        Dim rdBuscar As SqlDataReader

        Try
            Rutas.Nombre = txtRuta.Text
            cnConn.ConnectionString = CitraConnection
            Dim IdRuta As Integer = BuscarIntDatoCitra("SELECT IDRUTA FROM RUTAS WHERE NOMBRE='" & Rutas.Nombre & "'")

            strSql = "Select * from RutasOpto WHERE IdRuta=" & IdRuta

            cmdBuscar.Connection = cnConn
            cmdBuscar.CommandText = strSql


            cnConn.Open()
            rdBuscar = cmdBuscar.ExecuteReader

            Do While rdBuscar.Read()
                dgOptocontrol.Rows.Add(rdBuscar("ID_Ruta").ToString.Trim, rdBuscar("Cod_Ruta").ToString.Trim, rdBuscar("Descripcion").ToString.Trim)
            Loop
        Catch ex As Exception
            MessageBox.Show(ex.Message, "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cnConn.State = ConnectionState.Open Then
                cnConn.Close()
            End If
        End Try
    End Sub
    Sub CargaRutas()
        Windows.Forms.Cursor.Current = Cursors.WaitCursor
        dgBuscarRuta.Rows.Clear()

        Dim Filtro As String
        Filtro = UCase(txtBuscarRuta.Text)
        Filtro = "%" & Filtro.Replace(" ", "%") & "%"

        Dim cnConn As New SqlConnection
        Dim strSql As String
        Dim cmdBuscar As New SqlCommand
        Dim rdBuscar As SqlDataReader

        Try
            cnConn.ConnectionString = CitraConnection
            strSql = "Select * from Rutas WHERE UPPER(Nombre) LIKE '" & Filtro & "' " & mstrCondicion

            cmdBuscar.Connection = cnConn
            cmdBuscar.CommandText = strSql


            cnConn.Open()
            rdBuscar = cmdBuscar.ExecuteReader

            Do While rdBuscar.Read()
                dgBuscarRuta.Rows.Add(rdBuscar("Nombre").ToString.Trim)
                dgBuscarRuta.CurrentCell = dgBuscarRuta.Rows(0).Cells(0)
            Loop
        Catch ex As Exception
            MessageBox.Show(ex.Message, "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cnConn.State = ConnectionState.Open Then
                cnConn.Close()
            End If
        End Try
    End Sub
    Sub CargaOptocontrol()
        Windows.Forms.Cursor.Current = Cursors.WaitCursor
        dgBuscarOptocontrol.Rows.Clear()

        Dim Filtro As String
        Filtro = UCase(txtBuscarOptocontrol.Text)
        Filtro = "%" & Filtro.Replace(" ", "%") & "%"

        Dim cnConn As New SqlConnection
        Dim strSql As String
        Dim cmdBuscar As New SqlCommand
        Dim rdBuscar As SqlDataReader

        Try
            cnConn.ConnectionString = OptocontrolConnection
            strSql = "Select * from Ruta WHERE UPPER(COD_RUTA) LIKE '" & Filtro & "' " & mstrCondicion & " OR UPPER(DESCRIPCION) LIKE '" & Filtro & "' " & mstrCondicion
            
            cmdBuscar.Connection = cnConn
            cmdBuscar.CommandText = strSql


            cnConn.Open()
            rdBuscar = cmdBuscar.ExecuteReader

            Do While rdBuscar.Read()
                dgBuscarOptocontrol.Rows.Add(rdBuscar("Cod_Ruta").ToString.Trim, rdBuscar("Descripcion").ToString.Trim)
                dgBuscarOptocontrol.CurrentCell = dgBuscarOptocontrol.Rows(0).Cells(1)
            Loop
        Catch ex As Exception
            MessageBox.Show(ex.Message, "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cnConn.State = ConnectionState.Open Then
                cnConn.Close()
            End If
        End Try
    End Sub

    Private Sub txtBuscarOptocontrol_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles txtBuscarOptocontrol.KeyPress
        If e.KeyChar = Chr(Keys.Enter) Then
            SeleccionarOptocontrol()
        ElseIf e.KeyChar = Chr(Keys.Escape) Then
            panelOptocontrol.Visible = False
            txtOptocontrol.SelectAll()
            txtOptocontrol.Focus()

        End If

    End Sub

    Private Sub txtBuscarOptocontrol_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtBuscarOptocontrol.TextChanged
        CargaOptocontrol()
    End Sub

    Private Sub txtBuscarRuta_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles txtBuscarRuta.KeyPress
        If e.KeyChar = Chr(Keys.Enter) Then
            SeleccionarRuta()
        ElseIf e.KeyChar = Chr(Keys.Escape) Then
            PanelRutas.Visible = False
            txtRuta.SelectAll()
            txtRuta.Focus()
        End If
    End Sub
    Private Sub txtBuscarCliente_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles txtBuscarCliente.KeyPress
        If e.KeyChar = Chr(Keys.Enter) Then
            SeleccionarCliente()
        ElseIf e.KeyChar = Chr(Keys.Escape) Then
            PanelClientes.Visible = False
            txtCliente.SelectAll()
            txtCliente.Focus()
        End If
    End Sub

    Private Sub txtBuscarRuta_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtBuscarRuta.TextChanged
        CargaRutas()

    End Sub

    Private Sub RegresarToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles RegresarToolStripMenuItem.Click
        Close()

    End Sub

    Private Sub ToolStripButton1_Click(sender As System.Object, e As System.EventArgs) Handles ToolStripButton1.Click
        Close()
    End Sub

    Private Sub dgBuscarRuta_CellContentClick(sender As System.Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgBuscarRuta.CellContentClick

    End Sub
    Private Sub dgBuscarRuta_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles dgBuscarRuta.DoubleClick
        SeleccionarRuta()
    End Sub

    Private Sub dgBuscarRuta_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles dgBuscarRuta.KeyPress
        SeleccionarRuta()
    End Sub
    Private Sub DgProductos_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles DgProductos.DoubleClick
        SeleccionarCliente()
    End Sub

    Private Sub DgProductos_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles DgProductos.KeyPress
        SeleccionarCliente()
    End Sub
    Sub SeleccionarRuta()
        Dim valorCell As Integer
        Dim valorRow As DataGridViewRow

        valorCell = dgBuscarRuta.CurrentCell.ColumnIndex
        valorRow = dgBuscarRuta.CurrentRow

        Dim RutaMostrar As String = dgBuscarRuta.Rows(valorRow.Index).Cells(0).Value.ToString.Trim
        txtRuta.Text = RutaMostrar

        PanelRutas.Visible = False
        txtRuta.SelectAll()
        txtKms.Focus()
    End Sub
    Sub SeleccionarCliente()
        Dim valorCell As Integer
        Dim valorRow As DataGridViewRow

        valorCell = DgProductos.CurrentCell.ColumnIndex
        valorRow = DgProductos.CurrentRow

        Dim ClienteMostrar As String = DgProductos.Rows(valorRow.Index).Cells(1).Value.ToString.Trim
        txtCliente.Text = ClienteMostrar

        PanelClientes.Visible = False
        txtMapa.SelectAll()
        txtMapa.Focus()
    End Sub

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        PanelRutas.Visible = True
        txtBuscarRuta.Clear()
        txtBuscarRuta.Focus()
    End Sub
    Sub SeleccionarOptocontrol()
        Dim valorCell As Integer
        Dim valorRow As DataGridViewRow

        valorCell = dgBuscarOptocontrol.CurrentCell.ColumnIndex
        valorRow = dgBuscarOptocontrol.CurrentRow

        Dim OptocontrolSelect As String = dgBuscarOptocontrol.Rows(valorRow.Index).Cells(1).Value.ToString.Trim
        txtOptocontrol.Text = OptocontrolSelect

        panelOptocontrol.Visible = False
        txtOptocontrol.SelectAll()
        txtOptocontrol.Focus()
    End Sub
    Private Sub dgBuscarOptocontrol_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles dgBuscarOptocontrol.DoubleClick
        SeleccionarOptocontrol()
    End Sub

    Private Sub dgBuscarOptocontrol_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles dgBuscarOptocontrol.KeyPress
        SeleccionarOptocontrol()
    End Sub

    Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
        panelOptocontrol.Visible = True
        txtBuscarOptocontrol.Clear()
        txtBuscarOptocontrol.Focus()
    End Sub

    Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click
        Dim Nombre As String = txtOptocontrol.Text.Trim
        Dim Codigo As String = BuscarStrDatoOptoControl("RUTA", "COD_RUTA", "DESCRIPCION='" & Nombre & "'")
        If Codigo <> "" Then
            dgOptocontrol.Rows.Add(0, Codigo.Trim, txtOptocontrol.Text.Trim)
        Else
            MsgBox("La ruta de OptoControl seleccionada no existe", MsgBoxStyle.Exclamation, "Aviso")
        End If
    End Sub

    Private Sub ToolStripButton2_Click(sender As System.Object, e As System.EventArgs) Handles ToolStripButton2.Click
        Guardar()


    End Sub
    Sub Guardar()

        If RutaNueva = False Then
            Gatter()
            Dim IDRuta As Integer = BuscarIntDatoCitra("SELECT IDRUTA FROM RUTAS WHERE NOMBRE='" & Rutas.Nombre & "'")
            Rutas.IdRuta = IDRuta
            Rutas.Actualizar()

            Bitacora.RegistraActividad("Actualizó la ruta " & txtRuta.Text.Trim)


        Else
            Dim CodigoV As String = Mid(txtAdminPAQ.Text.Trim, 1, 3)
            Dim cidValor As Integer = BuscarIntDatoCitra("SELECT cidValor01 FROM MGW10020 WHERE cCodigoV01='" & CodigoV & "'")
            If cidValor > 0 Then
                MsgBox("El codigo de AdminPAQ ya existe", MsgBoxStyle.Exclamation, "Aviso")
                txtAdminPAQ.SelectAll()
                txtAdminPAQ.Focus()
                Exit Sub
            End If

            cidValor = (BuscarIntDatoCitra("SELECT MAX(CIDVALOR01) AS CIDVALOR01 FROM MGW10020")) + 1
            Dim ValorCl As String = txtRuta.Text.Trim
            Call SetSqlAdminPAQ("INSERT INTO MGW10020 VALUES (" & cidValor & ",'" & ValorCl & "',7,'" & CodigoV & "')")
            Gatter()

            Rutas.IdRuta = (BuscarIntDatoCitra("SELECT MAX(IDRUTA) AS IDRUTA FROM RUTAS")) + 1
            Rutas.Insertar()

            Bitacora.RegistraActividad("Creó la ruta " & txtRuta.Text.Trim)
        End If

        'Guarda Rutas de Optocontrol
        Call SetSqlCitra("DELETE FROM RUTASOPTO WHERE IDRuta=" & Rutas.IdRuta)
        If dgOptocontrol.Rows.Count > 0 Then
            Dim i As Integer = 0
            Dim Opto As New clsCatalogoRutasOpto
            For i = 0 To dgOptocontrol.Rows.Count - 1
                Opto.IdRutaOpto = (BuscarIntDatoCitra("SELECT MAX(IDRUTAOPTO) AS IDRUTA FROM RUTASOPTO")) + 1
                Opto.IdRuta = Rutas.IdRuta
                Opto.Id_Ruta = dgOptocontrol.Rows(i).Cells(0).Value.ToString.Trim
                Opto.Cod_Ruta = dgOptocontrol.Rows(i).Cells(1).Value.ToString.Trim
                Opto.Descripcion = dgOptocontrol.Rows(i).Cells(2).Value.ToString.Trim
                Opto.Insertar()
            Next
        End If
    End Sub
    Private Sub Gatter()
        Rutas.Nombre = txtRuta.Text.Trim
       
        Rutas.Kilometros = CDbl(txtKms.Text)
        Rutas.cidValor01 = BuscarIntDatoCitra("SELECT cidValor01 from MGW10020 WHERE cCodigoV01='" & txtAdminPAQ.Text.Trim & "'")
        Rutas.cCodigoV01 = txtAdminPAQ.Text.Trim
        If dgOptocontrol.Rows.Count > 0 Then
            Rutas.cidOpto = 1
        Else
            Rutas.cidOpto = 0
        End If

        Rutas.Imagen = txtMapa.Text.Trim
        Rutas.Importe01 = txtHoras.Text
        Rutas.Importe02 = 0
        Rutas.Importe03 = 0
        Rutas.Importe04 = 0
        Rutas.Importe05 = 0
        Rutas.Texto01 = BuscarStrDatoCitra("MGW10002", "cCodigoC01", "cRazonSo01='" & txtCliente.Text.Trim & "'")
        Rutas.Texto02 = ""
        Rutas.Texto03 = ""
        Rutas.Texto04 = ""
        Rutas.Texto05 = ""
    End Sub

    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        Dim oFD As New OpenFileDialog
        With oFD
            .Title = "Seleccionar fichero"
            .Filter = "Ficheros de texto (*.jpeg;*.jpg)|*.jpeg;*.jpg" & _
                      "|Todos los ficheros (*.*)|*.*"
            .FileName = txtMapa.Text
            If .ShowDialog = System.Windows.Forms.DialogResult.OK Then
                txtMapa.Text = .FileName
            End If
        End With
        CargaImagen()
    End Sub

    Private Sub DesdeConopToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs)
       

    End Sub

    Private Sub DesdeAdminPAQToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs)

    End Sub

    Private Sub NuevaToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles NuevaToolStripMenuItem.Click
        txtRuta.Text = ""
        txtRuta.Focus()
    End Sub

    Private Sub GuardarToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles GuardarToolStripMenuItem.Click
        Guardar()

    End Sub

    Private Sub ToolStripButton4_Click(sender As System.Object, e As System.EventArgs) Handles ToolStripButton4.Click
         Eliminar()
    End Sub

    Private Sub EliminarToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles EliminarToolStripMenuItem.Click
        Eliminar()

    End Sub
    Sub Eliminar()
        Dim idRuta As Integer = BuscarIntDatoCitra("SELECT IdRuta FROM Rutas WHERE Nombre='" & txtRuta.Text.Trim & "'")
        Dim Asignadas As Integer = 0
        Asignadas = BuscarIntDatoCitra("SELECT COUNT(*) FROM AUTOBUSES WHERE IDRuta=" & idRuta)
        If Asignadas > 0 Then
            MsgBox("No se puede eliminar la ruta seleccionada porque ya tiene Autobuses asignados", MsgBoxStyle.Exclamation, "Aviso")
            Bitacora.RegistraActividad("Intentó Eliminar la ruta " & txtRuta.Text.Trim)
            Exit Sub
        Else
            Call SetSqlCitra("DELETE FROM RUTAS WHERE IdRuta=" & idRuta)
            Call SetSqlCitra("DELETE FROM RUTASOPTO WHERE IDRuta=" & idRuta)
            Bitacora.RegistraActividad("Eliminó la ruta " & txtRuta.Text.Trim)
            CargaRutas()
            txtRuta.Clear()
            txtRuta.Focus()
        End If
    End Sub

    Private Sub Button5_Click(sender As System.Object, e As System.EventArgs) Handles Button5.Click
        PanelClientes.Visible = True
        txtBuscarCliente.Clear()
        txtBuscarCliente.Focus()
    End Sub

    Private Sub txtBuscarCliente_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtBuscarCliente.TextChanged
        CargaClientes()

    End Sub
End Class